﻿//********************************************************************************//
//创建日期：<创建日期，2017-10-27>
//创建作者：<孙雪婷，sunxueting@shanghai3h.com>
//功能说明：计划通知单
//********************************************************************************//

using System;
using System.Data;
using System.IO;
using BLL;
using BLL.Access;
using DevExpress.Web;
using Entity;
using NPOI.HSSF.UserModel;
using Web.Class;

namespace Web.Pages.YongShui.BaoBiaoGL.DanJuDY
{
    public partial class JiHuaTZD : BasePage
    {
        #region 属性

        /// <summary>
        ///     保存结果集1
        /// </summary>
        private DataTable dtGrid
        {
            get { return ViewState["dtGrid"] as DataTable; }
            set { ViewState["dtGrid"] = value; }
        }

        #endregion

        #region 变量

        /// <summary>
        ///     用户信息
        /// </summary>
        private YS_YG_YONGHUXX _YS_YG_YONGHUXX = new YS_YG_YONGHUXX();

        /// <summary>
        ///     用户信息bll
        /// </summary>
        private YS_YG_YONGHUXXBLL _YS_YG_YONGHUXXBLL = new YS_YG_YONGHUXXBLL();

        /// <summary>
        ///     管理部门
        /// </summary>
        private YS_JX_ZHANDIAN _YS_JX_ZHANDIAN = new YS_JX_ZHANDIAN();

        /// <summary>
        ///     管理部门bll
        /// </summary>
        private YS_JX_ZHANDIANBLL _YS_JX_ZHANDIANBLL = new YS_JX_ZHANDIANBLL();

        /// <summary>
        ///     管理块
        /// </summary>
        private YS_JX_GUANLIK _YS_JX_GUANLIK = new YS_JX_GUANLIK();

        /// <summary>
        ///     管理块BLL
        /// </summary>
        private readonly YS_JX_GUANLIKBLL _YS_JX_GUANLIKBLL = new YS_JX_GUANLIKBLL();


        /// <summary>
        ///     存储过程调用类
        /// </summary>
        private SpHelper _SpHelper = new SpHelper();

        #endregion

        #region 事件

        /// <summary>
        ///     页面加载事件
        /// </summary>
        protected void Page_Load(object sender, EventArgs e)
        {
            //第一次页面加载
            if (!IsPostBack)
                InitData();
            ScriptManager1.RegisterPostBackControl(btnExport);
            if (dtGrid != null)
            {
                gvYongHuXX.DataSource = dtGrid;
                gvYongHuXX.DataBind();
            }
        }


        /// <summary>
        ///     查询信息
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        protected void btnChaXun_Click(object sender, EventArgs e)
        {
            if (txtShuiLiangYM.Text != "")
                LoadGridData();
        }


        /// <summary>
        ///     tab1导出excel
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        protected void btnExportExcel_Click(object sender, EventArgs e)
        {
            if (dtGrid == null || dtGrid.Rows.Count < 1)
                PageTools.AlertMsg(this, "没有符合的数据导出");
            else
                ExportExcel(dtGrid);
        }

        private void ExportExcel(DataTable dt)
        {
            var wb = new HSSFWorkbook();
            var sheet = (HSSFSheet)wb.CreateSheet("Sheet1"); //创建工作表
            sheet.CreateFreezePane(0, 1); //冻结列头行
            var rowTitle = (HSSFRow)sheet.CreateRow(0); //创建列头行
            var columns = new[]
            {
                "业务管理部门", "业务部门邮编", "业务部门地址", "业务员", "联系电话", "用户编号", "用户名", "用户邮编", "用户地址", "考核水表数", "年", "一月", "二月",
                "三月", "四月", "五月", "六月", "七月", "八月", "九月", "十月", "十一月", "十二月"
            };
            for (int i = 0; i < columns.Length; i++)
            {
                var cellTitle = (HSSFCell)rowTitle.CreateCell(i);
                cellTitle.SetCellValue(columns[i]);
            }
            var index = 1;
            foreach (DataRow row in dt.Rows)
            {
                var rowData = (HSSFRow) sheet.CreateRow(index);
                for (int i = 0; i < row.ItemArray.Length; i++)
                {
                    var cellData = (HSSFCell)rowData.CreateCell(i);
                    cellData.SetCellValue(row[i].ToString());
                    
                }
                index++;
            }
            MemoryStream stream = new MemoryStream();
            wb.Write(stream);
            var buf = stream.ToArray();
            Response.ContentType = "application/vnd.ms-excel";
            Response.AddHeader("Content-Disposition", "attachment;filename=" + txtShuiLiangYM.Text + "年自来水用水计划.xls");
            Response.BinaryWrite(buf);
        }

        #endregion

        #region 方法

        /// <summary>
        ///     初始化数据
        /// </summary>
        private void InitData()
        {
            gvYongHuXX.SettingsPager.Mode = GridViewPagerMode.EndlessPaging;
            //水量年月
            var water_y_m = BusinessTool.YS_GETSETTINGSVALUE(eSETTINGS_KEYNAME.WATER_Y_M.ToString());
            txtShuiLiangYM.Text = water_y_m.Substring(0,4);
            // 管理块
            cmbGuanLiK.Items.Clear();
            cmbGuanLiK.Items.Add("全部", "00");
            var dtGuanLiK = _YS_JX_GUANLIKBLL.SelectYS_JX_GUANLIKsDynamic("i_jlzt=0", "S_GUANLIKH");
            for (var i = 0; i < dtGuanLiK.Rows.Count; i++)
                cmbGuanLiK.Items.Add(dtGuanLiK.Rows[i]["S_GUANLIKMC"].ToString(),
                    dtGuanLiK.Rows[i]["S_GUANLIKH"].ToString());
            cmbGuanLiK.SelectedIndex = cmbGuanLiK.Items.Count > 1 ? 1 : 0;
        }


        /// <summary>
        ///     加载查询数据
        /// </summary>
        private void LoadGridData()
        {
            var guanlikuai = cmbGuanLiK.SelectedItem.Value.ToString();
            var year = txtShuiLiangYM.Text;
            if (RadioButton1.Checked)
            {
                dtGrid = new SqlHelper().P_YS_GETUSERPLANE(year, guanlikuai, "-1", "0", null).Tables[0];
            }
            else if (RadioButton2.Checked)
            {
                dtGrid = new SqlHelper().P_YS_GETUSERPLANE(year, guanlikuai, txtskhy.Text, "0", null).Tables[0];
            }
            else if (RadioButton3.Checked)
            {
                dtGrid = new SqlHelper().P_YS_GETUSERPLANE(year, guanlikuai, "-2", "1", null).Tables[0];
            }
            else if (rbSelYongHuH.Checked)
            {
                dtGrid = new SqlHelper().P_YS_GETUSERPLANE(year, guanlikuai, "-3", txtSelYongHuH.Text, null).Tables[0];
            }
            else
            {
                dtGrid = new SqlHelper().P_YS_GETUSERPLANE(year, guanlikuai, "-2", "0", null).Tables[0];
            }
            
            gvYongHuXX.DataSource = dtGrid;
            gvYongHuXX.DataBind();
            if (dtGrid != null && dtGrid.Rows.Count > 0)
            {
                var yonghuh = dtGrid.Rows[0]["S_YONGHUH"].ToString();
                txtYHH1.Text = yonghuh;
                txtYHH2.Text = yonghuh;
                zjl.Text = "共" + dtGrid.Rows.Count + "条";
                gvYongHuXX.FocusedRowIndex = 0;
            }
        }


        /// <summary>
        ///     根据用户号定位
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        protected void btnDingWei_Click(object sender, EventArgs e)
        {
            if (txtYongHuH.Text.Trim() != "")
            {
                var index = PageTools.FocusOpreateRecord(dtGrid, "S_YONGHUH", txtYongHuH.Text, true);
                if (index == -1)
                    PageTools.AlertMsg(this, "当前数据中没有你要找的数据,请检查你输入的条件是否正确。");
                else
                {
                    gvYongHuXX.SettingsBehavior.AllowFocusedRow = true;
                    gvYongHuXX.Selection.SelectRow(index);
                    //gvYongHuXX.FocusedRowIndex = index;
                    gvYongHuXX.ScrollToVisibleIndexOnClient = index;
                }
            }
        }


        protected void btnExport_Click(object sender, EventArgs e)
        {
            if (dtGrid == null || dtGrid.Rows.Count < 1)
                PageTools.AlertMsg(this, "没有符合的数据导出");
            else
            {
                ExportExcel(dtGrid);
            }
        }

        #endregion
    }
}